# This file is used to create the user_role table in the database.

'''
-- 用户角色表    
CREATE TABLE user_role (    
    role_id SERIAL PRIMARY KEY,    
    role_name VARCHAR(20) NOT NULL,    
    role_desc VARCHAR(100) NOT NULL    
);
'''


class UserRoleTableMethod:
    def __init__(self, conn, cur):
        self.cursor = cur
        self.conn = conn


    def insert_user_role(self, role_name, role_desc):
        """
        插入用户角色信息
        :param role_name: 角色名称
        :param role_desc: 角色描述
        :return:
        """
        sql = "INSERT INTO user_role (role_name, role_desc) VALUES (%s, %s) RETURNING role_id"
        params = (role_name, role_desc)
        self.cursor.execute(sql, params)
        new_role_id = self.cursor.fetchone()["role_id"]
        self.conn.commit()
        return new_role_id

    def get_user_role_by_id(self, role_id):
        """
        根据角色ID获取角色信息
        :param role_id: 角色ID
        :return:
        """
        sql = "SELECT * FROM user_role WHERE role_id = %s"
        params = (role_id,)
        self.cursor.execute(sql, params)
        result = self.cursor.fetchone()
        return result

    def get_user_role_by_name(self, role_name):
        """
        根据角色名称获取角色信息
        :param role_name: 角色名称
        :return:
        """
        sql = "SELECT * FROM user_role WHERE role_name = %s"
        params = (role_name,)
        self.cursor.execute(sql, params)
        result = self.cursor.fetchone()
        return result

    def get_all_user_role(self,limit=0, page=1):
        """
        获取所有角色信息
        :return:
        """
        sql = "SELECT * FROM user_role LIMIT %s OFFSET %s"
        params = (limit, (page-1)*limit)
        self.cursor.execute(sql, params)
        result = self.cursor.fetchall()
        return result


    def update_user_role(self, role_id, role_name, role_desc):
        """
        更新角色信息
        :param role_id: 角色ID
        :param role_name: 角色名称
        :param role_desc: 角色描述
        :return:
        """
        sql = "UPDATE user_role SET role_name = %s, role_desc = %s WHERE role_id = %s"
        params = (role_name, role_desc, role_id)
        self.cursor.execute(sql, params)
        self.conn.commit()
        return True

    def delete_user_role_by_id(self, role_id):
        """
        删除角色信息
        :param role_id: 角色ID
        :return:
        """
        sql = "DELETE FROM user_role WHERE role_id = %s"
        params = (role_id,)
        try:
            self.cursor.execute(sql, params)

        except Exception as e:
            print(e)
            return False
        else:
            self.conn.commit()
            return True